package com.tiancikeji.zaoke.app.dao.user.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.tiancikeji.zaoke.app.dao.user.UserDao;
import com.tiancikeji.zaoke.app.model.user.User;

public class UserDaoImpl extends JdbcDaoSupport implements UserDao{

	private static final String TABLE = "`zaoke`.`user`";
	
	private static final String QUERY_VIEW = "`user_id`, `card_id`, `user_name`, `cell_phone`, `zone_id`, `balance`, `state`, `register_time`, `last_buy_time`, `mod_time`, `consume`, `password`, `pay_way`";

	public User getUserInfo(long userid){
		String sql = "select " + QUERY_VIEW + " from " + TABLE + " where `user_id` = ? order by mod_time desc limit 1";
		
		List<User> users = getJdbcTemplate().query(sql, new Object[]{userid}, new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setUserId(rs.getLong("user_id"));
				user.setCardId(rs.getString("card_id"));
				user.setUserName(rs.getString("user_name"));
				user.setCellPhone(rs.getString("cell_phone"));
				user.setZoneId(rs.getLong("zone_id"));
				user.setBalance(rs.getDouble("balance"));
				user.setState(rs.getInt("state"));
				user.setRegisterTime(rs.getDate("register_time"));
				user.setLastBuyTime(rs.getDate("last_buy_time"));
				user.setModTime(rs.getDate("mod_time"));
				user.setConsume(rs.getDouble("consume"));
				user.setPassword(rs.getString("password"));
				user.setPayway(rs.getInt("pay_way"));
				
				return user;
			}
		});
		
		if(users != null && users.size() > 0)
			return users.get(0);
		else
			return null;
	}

	public long addAutoUser(final User user) {
		final String sql = "insert into " + TABLE + " (`user_name`, `state`, `register_time`, `mod_time`) values(?, ?, ?,?)";
		KeyHolder holder = new GeneratedKeyHolder();
		int count = getJdbcTemplate().update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setObject(1, user.getUserName());
				ps.setObject(2, user.getState());
				ps.setObject(3, user.getRegisterTime());
				ps.setObject(4, user.getModTime());
				return ps;
			}
		}, holder);
		
		if(count > 0 && holder.getKey() != null)
			return holder.getKey().longValue();
		
		return 0;
	}

	public boolean updateCardIdByUserId(String cId,  Double money, long userId) {
		String sql = "update " + TABLE + "set card_id = ?, balance = balance + ?, mod_time = now() where user_id = ?";
		int cnt = getJdbcTemplate().update(sql, new Object[]{cId, money, userId});
		return cnt > 0;
	}

	public boolean updateUserPhoneNPassword(User user) {
		String sql = "update " + TABLE + "set password = ?, cell_phone = ?, state = ?, mod_time = now() where user_id = ?";
		int cnt = getJdbcTemplate().update(sql, new Object[]{user.getPassword(), user.getCellPhone(), user.getState(), user.getUserId()});
		return cnt > 0;
	}

	public int getPhoneCount(String phone, Object[] states) {
		String sql = "select count(1) from " + TABLE + " where cell_phone = " + phone + " and state in (";
		sql += StringUtils.join(states, ",");
		sql += ")";
		return getJdbcTemplate().queryForInt(sql);
	}

	public List<User> getUsersByPhone(String phone, String password, Object[] states) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE + " where `cell_phone` = ? and `password` = ? and `state` in (";
		sql += StringUtils.join(states, ",");
		sql += ") order by mod_time desc limit 1";
		
		return getJdbcTemplate().query(sql, new Object[]{phone, password}, new RowMapper<User>() {

			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setUserId(rs.getLong("user_id"));
				user.setCardId(rs.getString("card_id"));
				user.setUserName(rs.getString("user_name"));
				user.setCellPhone(rs.getString("cell_phone"));
				user.setZoneId(rs.getLong("zone_id"));
				user.setBalance(rs.getDouble("balance"));
				user.setState(rs.getInt("state"));
				user.setRegisterTime(rs.getDate("register_time"));
				user.setLastBuyTime(rs.getDate("last_buy_time"));
				user.setModTime(rs.getDate("mod_time"));
				user.setConsume(rs.getDouble("consume"));
				user.setPassword(rs.getString("password"));
				
				return user;
			}
		});
	}

	public boolean updatePassword(String password, User user) {
		String sql = "update " + TABLE + "set password = ?, mod_time = now() where user_id = ? and password = ?";
		int cnt = getJdbcTemplate().update(sql, new Object[]{password, user.getUserId(), user.getPassword()});
		return cnt > 0;
	}

	public void updateOrderTimeNZoneIdByUserId(long userId, Date orderTime, long zoneId, int payway) {
		String sql = "update " + TABLE + "set last_buy_time = ?, zone_id = ?, pay_way = ? where user_id = ? ";
		getJdbcTemplate().update(sql, new Object[]{orderTime, zoneId, payway, userId});		
	}

	public boolean rename(String name, User user) {
		String sql = "update " + TABLE + "set user_name = ?, mod_time = now() where user_id = ? and user_name = ?";
		int cnt = getJdbcTemplate().update(sql, new Object[]{name, user.getUserId(), user.getUserName()});
		return cnt > 0;
	}

	public boolean updateMoney(long userId, double money, double balance) {
		String sql = "update " + TABLE + "set balance = balance + ?, mod_time = now() where user_id = ? and balance = ?";
		int cnt = getJdbcTemplate().update(sql, new Object[]{money, userId, balance});
		return cnt > 0;
	}

	public boolean updateMoney(long userId, double money) {
		String sql = "update " + TABLE + "set balance = balance + ?, mod_time = now() where user_id = ?";
		int cnt = getJdbcTemplate().update(sql, new Object[]{money, userId});
		return cnt > 0;
	}
}
